﻿/***************************************************************
 * 开发人员：网魂小兵@http://www.mozlite.com
 * 
 *  SQL Server分页SQL字符串组合类。
 * 
 * 最后修改：2011-10-19
 * ************************************************************/
namespace Mozlite.Data.SqlDataProvider
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Collections.Specialized;
    /// <summary>
    /// 查询字符串组合类。
    /// </summary>
    public class SqlClientPageBuilder
    {
        /// <summary>
        /// 初始化类型<see cref="T:Mozlite.Data.SqlClientPageBuilder"/>。
        /// </summary>
        public SqlClientPageBuilder() { 
            IsDesc = true;
            PageSize = 20;
        }

        private List<string> wheres;
        /// <summary>
        /// 增加条件。
        /// </summary>
        /// <param name="where">条件列表。</param>
        /// <param name="args">条件值。</param>
        public void AppendWhere(string where, params object[] args) {
            if (string.IsNullOrEmpty(where))
                return;
            if (wheres == null)
                wheres = new List<string>();
            wheres.Add(string.Format(where, args));
        }

        private List<string> fields;
        private StringBuilder builder;
        /// <summary>
        /// 添加列。
        /// </summary>
        /// <param name="field">列名称。</param>
        public void AppendField(string field) {
            if (string.IsNullOrEmpty(field))
                return;
            if (fields == null)
                fields = new List<string>();
            if (fields.Contains(field, new IgnoreCaseStringComparer()))
                fields.Add(field);
        }
        /// <summary>
        /// 添加列。
        /// </summary>
        /// <param name="field">列名称。</param>
        public void AppendFields(string fields) {
            if (string.IsNullOrEmpty(fields))
                return;
            if (this.fields == null)
                this.fields = new List<string>();
            foreach (var field in fields.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
            {
                if (!this.fields.Contains(field.Trim(), new IgnoreCaseStringComparer()))
                    this.fields.Add(field.Trim());
            }
        }
        /// <summary>
        /// 获取和设置页码。
        /// </summary>
        public int PageIndex { get; set; }
        /// <summary>
        /// 获取和设置每页显示的记录数。
        /// </summary>
        public int PageSize { get; set; }
        private string whereTables;
        /// <summary>
        /// 获取或设置查询的条件数据表。
        /// </summary>
        public string WhereTables { get { if (string.IsNullOrEmpty(this.whereTables)) this.whereTables = this.fieldTables; return this.whereTables; } set { this.whereTables = value; } }
        private string fieldTables;
        /// <summary>
        /// 获取或设置查询的数据表。
        /// </summary>
        public string FieldTables { get { if (string.IsNullOrEmpty(this.fieldTables)) this.fieldTables = this.whereTables; return this.fieldTables; } set { this.fieldTables = value; } }
        /// <summary>
        /// 获取排序列名称，必须有一并且唯一一列。
        /// </summary>
        public string OrderBy { get; set; }
        /// <summary>
        /// 获取是否为降序排列。
        /// </summary>
        public bool IsDesc { get; set; }
        /// <summary>
        /// 获取或设置是否返回记录总数。
        /// </summary>
        public bool ReturnSize { get; set; }

        private void BuildFirstPage()
        {
            string whereSql = GetWhere();
            builder.AppendLine("SELECT TOP {0} {1} FROM {2} {3} ORDER BY {4} {5};", PageSize, GetFields(), FieldTables, whereSql, OrderBy, IsDesc ? "DESC" : "ASC");
            if(ReturnSize)
                builder.AppendLine("SELECT Count(*) FROM {0} {1};", FieldTables, whereSql);
        }

        private void BuildQueryPage()
        {
            string whereSql = GetWhere();
            string whereSqlAnd = whereSql;
            if (whereSql != null)
                whereSqlAnd += " AND ";
            else
                whereSqlAnd = "WHERE";
            string fields = GetFields();
            builder.AppendFormat("SELECT TOP {0} {1} FROM {2} {3} {4} ", PageSize, fields, FieldTables, whereSqlAnd, OrderBy);
            if (IsDesc)
                builder.AppendLine("< (SELECT MIN({0}) FROM (SELECT TOP {1} {0} FROM {2} {3} ORDER BY {0} DESC) AS IDX) ORDER BY {0} DESC;", OrderBy, PageSize * PageIndex, WhereTables ?? FieldTables, whereSql);
            else
                builder.AppendLine("> (SELECT MAX({0}) FROM (SELECT TOP {1} {0} FROM {2} {3} ORDER BY {0} ASC) AS IDX) ORDER BY {0} ASC;", OrderBy, PageSize * PageIndex, WhereTables ?? FieldTables, whereSql);
            if (ReturnSize)
                builder.AppendLine("SELECT Count(*) FROM {0} {1};", FieldTables, whereSql);
        }

        private string GetWhere() {
            if (wheres == null)
                return null;
            string qwhere = "WHERE ";
            qwhere += wheres.Join(" ");
            return qwhere;
        }

        private string GetFields() {
            if (fields == null)
                return "*";
            return fields.Join(",");
        }
        /// <summary>
        /// 返回分页查询字符串。
        /// </summary>
        /// <returns>返回分页查询字符串。</returns>
        public override string ToString()
        {
            builder = new StringBuilder();
            if (PageIndex == 0)
                BuildFirstPage();
            else
                BuildQueryPage();
            return builder.ToString();
        }
        /// <summary>
        /// 获取是否还没有添加WHERE语句。
        /// </summary>
        public bool IsWhereNull { get { return this.wheres == null; } }
    }
}
